﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using VNPT.Excel;
using System.Data;

namespace VNPT.Utility
{
    public static class ExcelHelper
    {
        public static DataTable GetData(string Path, string TableName)
        {
            if (System.IO.File.Exists(Path))
            {
                FileStream stream = File.Open(Path, FileMode.Open);//, FileMode.Open, FileAccess.Read);
                IExcelDataReader excelReader;
                if (Path.EndsWith(".xls"))
                {
                    excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
                }
                else
                {
                    if (Path.EndsWith(".xlsx"))
                    {
                        excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                    }
                    else
                    {
                        DevExpress.XtraEditors.XtraMessageBox.Show("File không hợp lệ!");
                        return null;
                    }
                }

                excelReader.IsFirstRowAsColumnNames = true;
                DataSet result = excelReader.AsDataSet();

                return result.Tables[TableName];
            }
            return null;
        }
        public static List<string> GetSheetName(string Path)
        {
            List<string> kq = new List<string>();
            if (System.IO.File.Exists(Path))
            {
                using (var stream = File.Open(Path, FileMode.Open))
                {//, FileMode.Open, FileAccess.Read);
                    IExcelDataReader excelReader;
                    if (Path.EndsWith(".xls"))
                    {
                        excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
                    }
                    else
                    {
                        if (Path.EndsWith(".xlsx"))
                        {
                            excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                        }
                        else
                        {
                            DevExpress.XtraEditors.XtraMessageBox.Show("File không hợp lệ!");
                            return null;
                        }
                    }
                    excelReader.IsFirstRowAsColumnNames = true;
                    var result = excelReader.AsDataSet();
                    foreach (var item in result.Tables)
                    {
                        kq.Add(((DataTable)item).TableName);
                    }
                    return kq;
                }
            }
            return null;
        }
    }
}
